package com.kehutong.payfees.service;

import java.io.File;
import java.io.InputStream;
import java.math.BigDecimal;
import java.time.LocalDate;
import java.time.format.DateTimeFormatter;
import java.util.*;
import java.util.function.Function;
import java.util.stream.Collectors;

import com.kehutong.common.util.Token;
import com.kehutong.payfees.entity.*;
import com.kehutong.payfees.enums.*;
import com.kehutong.payfees.util.BasicClient;
import com.kehutong.payfees.util.ExcelUtil;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.coraframework.converter.Converter;
import org.coraframework.converter.util.StringUtil;
import org.coraframework.inject.Inject;
import org.coraframework.json.JSONArray;
import org.coraframework.json.JSONObject;
import org.coraframework.mvc.http.result.Result;
import org.coraframework.mvc.webservice.WebService;
import org.coraframework.orm.Pool;
import org.coraframework.orm.jdbc.JdbcSession;
import org.coraframework.orm.jdbc.Page;
import org.coraframework.util.Objects;

import com.kehutong.common.ExcelHeaders;
import com.kehutong.common.math.CalculatorUtils;
import com.kehutong.common.urlconfig.FileUrlConfig;

/**
 * 处理临时收费
 */
@WebService("/payfees/charge/month/read")
public class ChargeMonthReadServiceImpl {

    @Inject
    private JdbcSession sqlSession;
    @Inject
    private BasicClient basicClient;
    @Inject
    private FileUrlConfig fileUrlConfig;
    @Inject
    private ChargeChooseServiceImpl chargeChooseService;

    public Object page(Token token, JSONObject jsonObject) throws Exception {
        return this.findChargeMonthReadPage(token, jsonObject);
    }

    public Object export(Token token, JSONObject jsonObject) throws Exception {
        List<ChargeMonthRead> list = this.findChargeMonthReadList(token, jsonObject);

        if (list.isEmpty()) {
            return Result.error(0, "无记录导出");
        }

        XSSFWorkbook wb = generateExportWorkBook(list);

        File tmpFile = ExcelUtil.writeXlsFile(wb, "月度抄表导出", ".xlsx");
        return Result.download(tmpFile);
    }

    public Object download(Token token, JSONObject jsonObject) throws Exception {
        Workbook wb = this.generateExportWorkBook(token, jsonObject);

        File tmpFile = ExcelUtil.writeXlsFile(wb, "月度抄表导入模板", ".xls");
        return Result.download(tmpFile);
    }

    public Object update(Token token, JSONObject jsonObject) throws Exception {
        BigDecimal lateRead = jsonObject.getBigDecimal("lateRead");
        if (lateRead == null) {
            return Result.error(0, "请确认 上次读数 非空");
        }
        if (lateRead != null && CalculatorUtils.getDecimalPlace(lateRead) > 1) {
            return Result.error(0, "请确认 上次读数 小数位最多支持1位");
        }
        if (lateRead != null && lateRead.compareTo(BigDecimal.ZERO) < 0) {
            return Result.error(0,"请确认 上次读数 不可为负数");
        }

        BigDecimal currentRead = jsonObject.getBigDecimal("currentRead");
        if (currentRead == null) {
            return Result.error(0, "请确认 本次读数 非空");
        }
        if (currentRead != null && CalculatorUtils.getDecimalPlace(currentRead) > 1) {
            return Result.error(0, "请确认 本次读数 小数位最多支持1位");
        }
        if (currentRead != null && currentRead.compareTo(BigDecimal.ZERO) < 0) {
            return Result.error(0,"请确认 本次读数 不可为负数");
        }

        ChargeMonthRead item = Pool.get(ChargeMonthRead.class, jsonObject.getString("id"));

        ChargeUserInstrument userInstrument = sqlSession.findOne(ChargeUserInstrument.class)
                .eq(jsonObject,"communityNo")
                .eq("id", jsonObject.getString("instrumentId"))
                .eq("deleted", false)
                .exe();

        if(Objects.isNull(userInstrument)){
            return Result.error(0, "未查询到相关入户表的信息");
        }

        LocalDate currentReadDate = Converter.toLocalDate(jsonObject.getString("currentReadDate"));

        // 本次日期≥仪表档案: 最近日期，则仪表档案. 更新记录
        LocalDate insReadDate = LocalDate.parse(userInstrument.getReadDate());
        if(currentReadDate.isAfter(insReadDate) || currentReadDate.equals(insReadDate)){
            userInstrument.setReadDate(DateTimeFormatter.ofPattern("yyyy-MM-dd").format(currentReadDate));
            userInstrument.setLateRead(currentRead);
        }
        sqlSession.updateById(userInstrument);

        item.setLateRead(lateRead);
        item.setLateReadDate(Converter.toLocalDate(jsonObject.getString("lateReadDate")));
        item.setCurrentRead(currentRead);
        item.setCurrentReadDate(currentReadDate);

        // 新的小计金额
        if(Objects.isNull(currentRead)){
            item.setLineMeasure(null);
            item.setBillNumber(null);
        }else if(Objects.nonNull(currentRead) && currentRead.compareTo(lateRead) > -1){
            BigDecimal lineMeasure = currentRead.subtract(lateRead);
            item.setLineMeasure(lineMeasure);
            item.setBillNumber(lineMeasure.multiply(item.getRatio()));
        }else if(Objects.nonNull(currentRead) &&
                currentRead.compareTo(lateRead) == -1){
            BigDecimal maxRead = new BigDecimal(item.getMaxRead().getDesc());
            BigDecimal lineMeasure = maxRead.add(BigDecimal.ONE).subtract(lateRead).add(currentRead);
            item.setLineMeasure(lineMeasure);
            item.setBillNumber(lineMeasure.multiply(item.getRatio()));
        }

        setDepartmentNo(item, token.getCompanyNo());

        sqlSession.updateById(item);

        List<ChargeBillItem> billItems = sqlSession.findArray(ChargeBillItem.class)
                .eq("monthReadNo", item.getId())
                .order("createTime", "desc")
                .exe();

        if(Objects.nonEmpty(billItems)){
            for (ChargeBillItem billItem : billItems) {
                ChargeBill bill = Pool.get(ChargeBill.class, billItem.getBillNo());
                if (bill.getPaidState() == BillPaidState.UNPAID) {
                    BigDecimal oldItemTotal = billItem.getSubTotal();
                    BigDecimal newItemTotal = BigDecimal.ZERO;

                    billItem.setLastReadNumber(lateRead);
                    billItem.setTheReadNumber(currentRead);
                    billItem.setAmount(item.getBillNumber());

                    ChargeChoose choose = Pool.get(ChargeChoose.class, billItem.getChooseId());
                    if (Objects.nonNull(billItem.getAmount())) {
                        if(Integer.valueOf(choose.getChargeMode().getId()) > 4) {
                            choose.setAmount(item.getBillNumber());
                            chargeChooseService.getTotal(choose);
                            newItemTotal = choose.getTotal();
                        }else{
                            newItemTotal = Objects.nonNull(billItem.getAmount()) ?
                                    billItem.getAmount().multiply(billItem.getUnitPrice()) : BigDecimal.ZERO.multiply(billItem.getUnitPrice());

                        }
                    }

                    billItem.setSubTotal(new BigDecimal(newItemTotal.toString())
                            .setScale(Integer.valueOf(choose.getUnitPrice().getId()),
                                    Integer.valueOf(choose.getComputeType().getId())));

                    bill.setTotal(bill.getTotal().subtract(oldItemTotal).add(billItem.getSubTotal()));

                    sqlSession.updateById(billItem);
                    sqlSession.updateById(bill);
                }
            }
        }

        return Result.success();
    }

    public Object readFile(Token token, JSONObject jsonObject) throws Exception {
        String[] headerArr = ExcelHeaders.CHARGE_MONTH_READ_EXCEL_HEADER;
        List<String> errMsgs = new ArrayList<>();

        InputStream is = fileUrlConfig.download(jsonObject.getString("id"));
        Workbook wb = WorkbookFactory.create(is);
        Sheet sheet = wb.getSheetAt(0);

        // 第一行说明 不读
        // 第二行表头
        Row firstRow = sheet.getRow(sheet.getFirstRowNum() + 1);

        int lateReadIndex = -1, readDateIndex = -1;
        for (int i = firstRow.getFirstCellNum(); i < firstRow.getLastCellNum(); i++) {
            firstRow.getCell(i).setCellType(CellType.STRING);
            String s = firstRow.getCell(i).getStringCellValue();
            if (i < headerArr.length) {
                if (!headerArr[i].equals(s)) {
                    errMsgs.add("请确认" + headerArr[i] + "为第" + i + 1 + "列");
                }
                if ("本次读数".equals(headerArr[i])) {
                    lateReadIndex = i;
                } else if ("本次读数日期".equals(headerArr[i])) {
                    readDateIndex = i;
                }
            }
        }

        // 表头校验结果
        if (!errMsgs.isEmpty()) {
            return Result.error(0, StringUtil.join(errMsgs, ";"));
        }

        List<JSONObject> jsons = new ArrayList<>();
        // 第三行开始遍历获取数据
        for (int rIndex = sheet.getFirstRowNum() + 2; rIndex <= sheet.getLastRowNum(); rIndex++) {
            Row row = sheet.getRow(rIndex);
            if (Objects.isNull(row)){
                continue;
            }
            Cell cell = row.getCell(0);
            if (Objects.isNull(cell)) {
                continue;
            }
            String readMonth = row.getCell(0).getStringCellValue();
            if (StringUtil.isBlank(readMonth)) {
                errMsgs.add("第" + rIndex + "行：抄表月份不能为空");
            }

            String userInstrumentId = row.getCell(1).getStringCellValue();
            if (StringUtil.isBlank(userInstrumentId)) {
                errMsgs.add("第" + rIndex + "行：入户表ID不能为空");
            }

            cell = row.getCell(lateReadIndex);
            cell.setCellType(CellType.STRING);
            BigDecimal lateRead = new BigDecimal(cell.getStringCellValue());
//            if (lateRead == null) {
//                errMsgs.add("第" + rIndex + "行：请确认 本次读数 非空");
//            }
            if (lateRead != null && CalculatorUtils.getDecimalPlace(lateRead) > 1) {
                errMsgs.add("第" + rIndex + "行：请确认本次读数 小数位最多支持1位");
            }
            if (lateRead != null && lateRead.compareTo(BigDecimal.ZERO) < 0) {
                errMsgs.add("第" + rIndex + "行：请确认本次读数 不可为负数");
            }
            LocalDate readDate = ExcelUtil.getLocalDateFromCell(row, readDateIndex);
            if (readDate == null) {
                errMsgs.add("第" + rIndex + "行：请确认 本次读数日期 非空，单元格设置为日期格式");
            }

            JSONObject json = new JSONObject();
            json.put("readMonth", readMonth);
            json.put("userInstrumentId", userInstrumentId);
            json.put("lateRead", lateRead);
            json.put("readDate", readDate);
            jsons.add(json);
        }

        // 数据读取结果
        if (!errMsgs.isEmpty()) {
            return Result.error(0, StringUtil.join(errMsgs, ";"));
        }

        for (JSONObject item: jsons) {
            String readMonth = item.getString("readMonth");
            String userInstrumentId = item.getString("userInstrumentId");

            ChargeUserInstrument ins = Pool.get(ChargeUserInstrument.class, userInstrumentId);

            if(ins.isCopyInstrument()){
                ChargeMonthRead entity = Pool.newInstance(ChargeMonthRead.class);
                entity.setAppType(AppType.ENTER);
                entity.setReadMonth(readMonth);
                entity.setHouseId(ins.getHouseId());
                entity.setCommunityNo(ins.getCommunityNo());
                entity.setContactName(ins.getContactName());
                entity.setInstrumentNo(ins.getInstrumentNo());
                entity.setInstrumentTypeId(ins.getInstrumentTypeId());
                entity.setMaxRead(ins.getMaxRead());
                entity.setInstrumentId(ins.getId());

                ChargeMonthRead read = sqlSession.findOne(ChargeMonthRead.class)
                        .eq("communityNo", ins.getCommunityNo())
                        .eq("houseId", ins.getHouseId())
//                        .eq("contactName", ins.getContactName())
                        .eq("instrumentId", ins.getId())
                        .eq("readMonth" ,readMonth)
                        .eq("deleted", false)
                        .exe();
                if(Objects.nonNull(read)){
                    read.setDeleted(true);
                    sqlSession.updateById(read);

                    entity.setLateRead(read.getLateRead());
                    entity.setLateReadDate(read.getLateReadDate());
                }else{
                    entity.setLateRead(ins.getLateRead());
                    entity.setLateReadDate(LocalDate.parse(ins.getReadDate()));
                }
                entity.setCurrentRead(item.getBigDecimal("lateRead"));
                entity.setCurrentReadDate((LocalDate) item.get("readDate"));
                entity.setRatio(ins.getRatio());
                entity.setWayType(MonthReadWayType.IMPORT);
                entity.setFeeState(false);
                entity.setLockState(false);
                entity.setOperator(token.getUsername());

                // 计算仪表行度、计费数量
                if(Objects.isNull(entity.getCurrentRead())){
                    entity.setLineMeasure(null);
                    entity.setBillNumber(null);
                }else if(Objects.nonNull(entity.getCurrentRead()) &&
                        entity.getCurrentRead().compareTo(entity.getLateRead()) > -1){
                    BigDecimal lineMeasure = entity.getCurrentRead().subtract(entity.getLateRead());
                    entity.setLineMeasure(lineMeasure);
                    entity.setBillNumber(lineMeasure.multiply(entity.getRatio()));
                }else if(Objects.nonNull(entity.getCurrentRead()) &&
                        entity.getCurrentRead().compareTo(entity.getLateRead()) == -1){
                    BigDecimal maxRead = new BigDecimal(entity.getMaxRead().getDesc());
                    BigDecimal lineMeasure = maxRead.add(BigDecimal.ONE).subtract(entity.getLateRead()).add(entity.getCurrentRead());
                    entity.setLineMeasure(lineMeasure);
                    entity.setBillNumber(lineMeasure.multiply(entity.getRatio()));
                }

                // 获取当前入户表 标准选用的ID
                List<ChargeChoose> chooses = sqlSession.findArray(ChargeChoose.class)
                        .eq("communityNo", ins.getCommunityNo())
                        .eq("instrumentId", ins.getId())
                        .eq("enableState", BillEnableState.ENABLE.getId())
                        .eq("chooseType", ChooseType.INSTRUMENT.getId())
                        .eq("deleted", false)
                        .exe();
                List<String> chooseIds = chooses.stream().map(ChargeChoose::getId).collect(Collectors.toList());
                entity.setChooseIds(chooseIds);

                setDepartmentNo(entity, token.getCompanyNo());
                //数据入库
                sqlSession.insert(entity);

                //修改入户表信息
                if(Objects.nonNull(entity.getCurrentRead())) {
                    ins.setLateRead(entity.getCurrentRead());
                    ins.setReadDate(DateTimeFormatter.ofPattern("yyyy-MM-dd").format(entity.getCurrentReadDate()));
                    sqlSession.updateById(ins);
                }
            }

        }

        StringBuilder successMsg = new StringBuilder();
        successMsg.append("新增");
        successMsg.append(jsons.size());
        successMsg.append("条抄表数据");

        return Result.success(successMsg);
    }

    private List<ChargeMonthRead> findChargeMonthReadList(Token token, JSONObject jsonObject) throws Exception {

        if(Objects.isEmpty(jsonObject.getString("communityNo"))) return new ArrayList<>();

        List<ChargeMonthRead> list = sqlSession.findArray(ChargeMonthRead.class)
                .eq(jsonObject, "communityNo", "readMonth", "appType",
                        "houseId", "instrumentTypeId", "contactName", "instrumentNo")
                .eq("deleted", false)
                .order("createTime", "desc")
                .exe();

        Set<String> houseIds = list.stream().map(ChargeMonthRead::getHouseId).collect(Collectors.toSet());

        // 存放房屋信息、方便后续使用
        Map<String, JSONObject> houseMap = basicClient.getHouseByIdsMap(token, new ArrayList<>(houseIds));

        list.forEach(item -> {
            item.setHouseInfo(basicClient.getHouseInfo(houseMap.get(item.getHouseId())));
            item.setInstrumentTypeName(Pool.get(ChargeInstrumentType.class,item.getInstrumentTypeId()).getName());
        });

        return list;
    }

    private Page<ChargeMonthRead> findChargeMonthReadPage(Token token, JSONObject jsonObject) throws Exception {

        if(Objects.isEmpty(jsonObject.getString("communityNo"))) return new Page<>();

        Page<ChargeMonthRead> page = sqlSession.findPage(ChargeMonthRead.class)
                .eq(jsonObject, "communityNo", "readMonth", "appType",
                        "houseId", "instrumentTypeId", "contactName", "instrumentNo")
                .eq("deleted", false)
                .order("createTime", "desc")
                .page(jsonObject)
                .exe();

        Set<String> houseIds = page.getList().stream().map(ChargeMonthRead::getHouseId).collect(Collectors.toSet());

        // 存放房屋信息、方便后续使用
        Map<String, JSONObject> houseMap = basicClient.getHouseByIdsMap(token, new ArrayList<>(houseIds));

        page.getList().forEach(item -> {

            JSONObject house = houseMap.get(item.getHouseId());
            if(Objects.nonEmpty(house)){
                item.setHouseInfo(basicClient.getHouseInfo(house));
            }
            item.setInstrumentTypeName(Pool.get(ChargeInstrumentType.class,item.getInstrumentTypeId()).getName());
        });

        return page;
    }

    private Workbook generateExportWorkBook(Token token, JSONObject jsonObject) throws Exception {
        // 生成模板文件
        HSSFWorkbook wb = new HSSFWorkbook();
        //创建工具
        CreationHelper helper = wb.getCreationHelper();
        //单元格样式类
        CellStyle dateCellStyle = wb.createCellStyle();
        //设置单元格格式
        dateCellStyle.setDataFormat(helper.createDataFormat().getFormat("yyyy-MM-dd"));

        HSSFCellStyle yellowStyle = wb.createCellStyle();
        yellowStyle.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
        yellowStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

        HSSFCellStyle chocolateStyle = wb.createCellStyle();
        chocolateStyle.setFillForegroundColor(IndexedColors.ROSE.getIndex());
        chocolateStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

        HSSFCellStyle blueStyle = wb.createCellStyle();
        blueStyle.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex());
        blueStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

        Sheet sheet = wb.createSheet();
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 11));
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 12, 13));

        // 首行 带颜色的说明文字
        Row firstRow = sheet.createRow(0);
        Cell firstRowCell = firstRow.createCell(0);
        firstRowCell.setCellValue("只读字段");
        firstRowCell.setCellStyle(yellowStyle);

        firstRowCell = firstRow.createCell(12);
        firstRowCell.setCellValue("录入字段");
        firstRowCell.setCellStyle(chocolateStyle);

        Row headerRow = sheet.createRow(1);
        int headerCellNum = 0;
        for (String s : ExcelHeaders.CHARGE_MONTH_READ_EXCEL_HEADER) {
            Cell cell = headerRow.createCell(headerCellNum++);
            cell.setCellValue(s);
        }

        int cellNum = 0;
        List<ChargeUserInstrument> instruments = sqlSession.findArray(ChargeUserInstrument.class)
                .eq(jsonObject, "communityNo", "instrumentTypeId", "appType")
                .eq("copyInstrument", true)
                .eq("deleted", false)
                .exe();

        if(Objects.nonEmpty(instruments)){
            //获取所有仪表类型的ID
            Set<String> typeIds = instruments.stream().map(ChargeUserInstrument :: getInstrumentTypeId).collect(Collectors.toSet());
            //获取所有房屋的ID
            Set<String> houseIds = instruments.stream().map(ChargeUserInstrument :: getHouseId).collect(Collectors.toSet());

            //通过仪表类型的ID 获取到所有的仪表类型信息
            List<ChargeInstrumentType> types = sqlSession.findArray(ChargeInstrumentType.class)
                    .in("id", typeIds)
                    .exe();

            // 存放房屋信息、方便后续使用
            Map<String, JSONObject> houseMap = basicClient.getHouseByIdsMap(token, new ArrayList<>(houseIds));
            Map<String,JSONObject> communityMap = basicClient.getCommunityMap(token);

            // 存放仪表类型信息、方便后续使用
            Map<String, ChargeInstrumentType> typeMap = types.stream().collect(Collectors.toMap(ChargeInstrumentType::getId, Function.identity()));

            // 入户表信息
            for (int i = 0; i < instruments.size(); i++) {
                ChargeUserInstrument entity = instruments.get(i);
                Row row = sheet.createRow(i + 2);
                cellNum = 0;

                row.createCell(cellNum++).setCellValue(jsonObject.getString("readMonth"));
                row.createCell(cellNum++).setCellValue(entity.getId());
                row.createCell(cellNum++).setCellValue(communityMap.get(entity.getCommunityNo()).getString("name"));
                row.createCell(cellNum++).setCellValue(basicClient.getHouseInfo(houseMap.get(entity.getHouseId())));
                row.createCell(cellNum++).setCellValue(entity.getContactName());
                row.createCell(cellNum++).setCellValue(entity.getInstrumentNo());
                row.createCell(cellNum++).setCellValue(AppType.ENTER.getDesc());
                row.createCell(cellNum++).setCellValue(typeMap.get(entity.getInstrumentTypeId()).getName());
                row.createCell(cellNum++).setCellValue(String.valueOf(entity.getRatio()));
                row.createCell(cellNum++).setCellValue(entity.getMaxRead().getDesc());
                row.createCell(cellNum++).setCellValue(entity.getLateRead().toString());

                Cell dateCell = row.createCell(cellNum++);
                dateCell.setCellStyle(dateCellStyle);
                dateCell.setCellValue(entity.getReadDate());
            }
        }

        // 自动列宽
        for (int i = 0; i < cellNum; i++) {
            if (i == 0) {
                // id列不调整
                continue;
            }
            // 自动列宽
            ExcelUtil.autoSizeColumn(sheet, i);
        }
        return wb;
    }

    public XSSFWorkbook generateExportWorkBook(List<ChargeMonthRead> list) {
        // 生成模板文件
        XSSFWorkbook wb = new XSSFWorkbook();
        //创建工具
        CreationHelper helper = wb.getCreationHelper();
        //单元格样式类
        CellStyle dateCellStyle = wb.createCellStyle();
        //设置单元格格式
        dateCellStyle.setDataFormat(helper.createDataFormat().getFormat("yyyy-MM-dd"));

        XSSFCellStyle wrapCellStyle = wb.createCellStyle();
        wrapCellStyle.setWrapText(true);

        Sheet sheet = wb.createSheet();

        Row headerRow = sheet.createRow(0);

        for (int i = 0; i < ExcelHeaders.CHARGE_MONTH_READ_EXCEL_IMPORT_HEADER.length; i++) {
            headerRow.createCell(i).setCellValue(ExcelHeaders.CHARGE_MONTH_READ_EXCEL_IMPORT_HEADER[i]);
        }

        int cellNum = 0;
        for (int i = 0; i < list.size(); i++) {
            cellNum = 0;
            ChargeMonthRead entity = list.get(i);
            Row row = sheet.createRow(i + 1);

            Cell dateCell = row.createCell(cellNum++);
            dateCell.setCellStyle(wrapCellStyle);
            dateCell.setCellValue(entity.getHouseInfo() + "\n" + entity.getContactName());

            dateCell = row.createCell(cellNum++);
            dateCell.setCellStyle(wrapCellStyle);
            dateCell.setCellValue(entity.getInstrumentNo() + "\n" + entity.getInstrumentTypeName());

            dateCell = row.createCell(cellNum++);
            dateCell.setCellStyle(wrapCellStyle);
            dateCell.setCellValue(entity.getLateRead() + "\n" + entity.getLateReadDate());

            dateCell = row.createCell(cellNum++);
            dateCell.setCellStyle(wrapCellStyle);
            dateCell.setCellValue(entity.getCurrentRead() + "\n" + entity.getCurrentReadDate());

            dateCell = row.createCell(cellNum++);
            dateCell.setCellStyle(wrapCellStyle);
            dateCell.setCellValue(entity.getLineMeasure() + "\n" + entity.getRatio() + "倍");

            row.createCell(cellNum++).setCellValue(String.valueOf(entity.getBillNumber()));

            dateCell = row.createCell(cellNum++);
            dateCell.setCellStyle(wrapCellStyle);
            dateCell.setCellValue(entity.getOperator() + "\n" + entity.getWayType().getDesc());
        }

        // 自动列宽
        for (int i = 0; i < cellNum; i++) {
            ExcelUtil.autoSizeColumn(sheet, i);
        }
        return wb;
    }

    private void setDepartmentNo(ChargeMonthRead chargeMonthRead, String companyNo) throws Exception {
        JSONObject community = basicClient.getCommunityById(companyNo,
                chargeMonthRead.getCommunityNo());
        if(Objects.nonEmpty(community)){
            String parent_id = community.getString("parent_id");
            if(Objects.nonEmpty(parent_id)){
                chargeMonthRead.setDepartmentNo(parent_id);
            }
            JSONArray parent_ids = community.getJSONArray("parent_ids");
            if(Objects.nonEmpty(parent_ids)){
                List<String> departmentNos = JSONObject.parseArray(parent_ids.toJSONString(), String.class);
                chargeMonthRead.setDepartmentNos(departmentNos);
            }
        }
    }
}
